/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import com.feib.stms.pagination.PaginatedList;

/**
 * @author Jimmy Liu
 *
 */
public class BaseJdbcDao extends SimpleJdbcDaoSupport 
{

	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	@Autowired
	@Qualifier("dataSource")
	public void setMyDataSource(DataSource dataSource)
	{
		super.setDataSource(dataSource);
	}

	protected Integer convertToInteger(Boolean bol)
	{
		if (null == bol)
			return null;

		return bol.booleanValue() ? new Integer(1) : new Integer(0);		
	}

	protected SqlQueryProvider createRecordCountSqlQueryProvider(SqlQueryProvider sqlQueryProvider)
	{
		List<Object> args = new ArrayList<Object>();
		if (CollectionUtils.isNotEmpty(sqlQueryProvider.getArgList()))
		{
			CollectionUtils.addAll(args, sqlQueryProvider.getArgs());
		}

		StringBuffer countSql = new StringBuffer();
		countSql.append("select count(1) from ( ");
		countSql.append(sqlQueryProvider.getSql());   
		countSql.append(") t ");        

		SqlQueryProvider result = new SqlQueryProvider(countSql.toString(), args);

		return result;
	}

	protected SqlQueryProvider createViewListSqlQueryProvider(SqlQueryProvider sqlQueryProvider, int start, int end)
	{

		List<Object> args = new ArrayList<Object>();
		if (CollectionUtils.isNotEmpty(sqlQueryProvider.getArgList()))
		{
			CollectionUtils.addAll(args, sqlQueryProvider.getArgs());
		}

		StringBuffer sqlWithLimitSize = new StringBuffer();
		//oracle
		sqlWithLimitSize.append("select * from ( ");
		sqlWithLimitSize.append("select rownum rowno, t.* from (");
		sqlWithLimitSize.append(sqlQueryProvider.getSql());        
		sqlWithLimitSize.append(") t ) where rowno >= ? and rowno <= ? ");

		args.add(new Integer(start));
		args.add(new Integer(end));

		SqlQueryProvider result = new SqlQueryProvider(sqlWithLimitSize.toString(), args);

		logger.debug("SQL: {}", result.getSql());
		logger.debug("Args: {}", result.getArgList());

		return result;
	}

	protected <E> PaginatedList<E> queryForPaginatedList(SqlQueryProvider sqlQueryProvider, RowMapper<E> rowMapper, int pageNumber, int pageSize)
	{

		int fullListSize = queryForRecordCount(sqlQueryProvider);
		if (0 == fullListSize)
			return null;

		PaginatedList<E> paginatedList = new PaginatedList<E>();
		paginatedList.setPageSize(pageSize);
		paginatedList.setPageNumber(pageNumber);
		paginatedList.setFullListSize(fullListSize);

		int start = paginatedList.getFirstIndexForCurrentPage();
		int end = paginatedList.getLastIndexForCurrentPage();

		SqlQueryProvider viewSql = this.createViewListSqlQueryProvider(sqlQueryProvider, start, end);
		List<E> viewList = this.getJdbcTemplate().query(viewSql.getSql(), viewSql.getArgs(), rowMapper);

		paginatedList.setPaginatedList(viewList);

		return paginatedList;
	}

	/**
	 * 查詢資料庫筆數 
	 * @param sqlQueryProvider
	 * @return
	 */
	protected int queryForRecordCount(SqlQueryProvider sqlQueryProvider)
	{
		SqlQueryProvider countSql = this.createRecordCountSqlQueryProvider(sqlQueryProvider);

		logger.debug("SQL: {}", countSql.getSql());
		logger.debug("Args: {}", countSql.getArgList());

		return this.getJdbcTemplate().queryForInt(countSql.getSql(), countSql.getArgs());
	}

	/**
	 * 計算筆數  using SimpleJdbcTemplate
	 * @param sqlQueryMapProvider
	 * @return
	 */
	protected SqlQueryMapProvider createRecordCountSqlQueryMapProvider(SqlQueryMapProvider sqlQueryMapProvider)
	{
		Map<String, Object> parameters = new HashMap<String, Object>();

		if (!sqlQueryMapProvider.getParameters().isEmpty())
		{
			parameters.putAll(sqlQueryMapProvider.getParameters());
		}
		StringBuffer countSql = new StringBuffer();
		countSql.append("select count(1) from ( ");
		countSql.append(sqlQueryMapProvider.getSql());   
		countSql.append(") t ");        

		SqlQueryMapProvider result = new SqlQueryMapProvider(countSql.toString(), parameters);

		return result;
	}
	/**
	 * 查詢指定範圍 using SimpleJdbcTemplate
	 * @param sqlQueryMapProvider
	 * @param start
	 * @param end
	 * @return
	 */
	protected SqlQueryMapProvider createViewListSqlQueryMapProvider(SqlQueryMapProvider sqlQueryMapProvider, int start, int end)
	{

		Map<String, Object> parameters = new HashMap<String, Object>();
		if (!sqlQueryMapProvider.getParameters().isEmpty())
		{
			parameters.putAll(sqlQueryMapProvider.getParameters());
		}

		StringBuffer sqlWithLimitSize = new StringBuffer();
		//oracle
		sqlWithLimitSize.append("select * from ( ");
		sqlWithLimitSize.append("select rownum rowno, t.* from (");
		sqlWithLimitSize.append(sqlQueryMapProvider.getSql());        
		sqlWithLimitSize.append(") t ) where rowno >= :START and rowno <= :END ");

		parameters.put("START",  start);

		parameters.put("END",  end);


		SqlQueryMapProvider result = new SqlQueryMapProvider(sqlWithLimitSize.toString(), parameters);

		logger.debug("SQL: {}", result.getSql());
		logger.debug("Parameters: {}", result.getParameters());

		return result;
	}
	/**
	 * 查詢結果 use getSimpleJdbcTemplate
	 * @param <E>
	 * @param sqlQueryMapProvider
	 * @param rowMapper
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	protected <E> PaginatedList<E> queryForPaginatedList(SqlQueryMapProvider sqlQueryMapProvider, RowMapper<E> rowMapper, int pageNumber, int pageSize)
	{

		int fullListSize = queryForRecordCount(sqlQueryMapProvider);
		if (0 == fullListSize)
			return null;

		PaginatedList<E> paginatedList = new PaginatedList<E>();
		paginatedList.setPageSize(pageSize);
		paginatedList.setPageNumber(pageNumber);
		paginatedList.setFullListSize(fullListSize);

		int start = paginatedList.getFirstIndexForCurrentPage();
		int end = paginatedList.getLastIndexForCurrentPage();

		SqlQueryMapProvider viewSql = this.createViewListSqlQueryMapProvider(sqlQueryMapProvider, start, end);
		List<E> viewList = this.getSimpleJdbcTemplate().query(viewSql.getSql(), rowMapper, viewSql.getParameters());

		paginatedList.setPaginatedList(viewList);

		return paginatedList;
	}

	/**
	 * 查詢資料庫筆數 use getSimpleJdbcTemplate
	 * @param SqlQueryMapProvider
	 * @return
	 */
	protected int queryForRecordCount(SqlQueryMapProvider sqlQueryMapProvider)
	{
		SqlQueryMapProvider countSql = this.createRecordCountSqlQueryMapProvider(sqlQueryMapProvider);

		logger.debug("SQL: {}", countSql.getSql());
		logger.debug("Parameters: {}", countSql.getParameters());

		return this.getSimpleJdbcTemplate().queryForInt(countSql.getSql(), countSql.getParameters());
	}



}
